# coding:utf-8
import requests
import json
import MySQLdb
import time
import os



# 下载图片
def img_download(img_name):
    net_img_path = "https://uimg-shefavors.oss-us-west-1.aliyuncs.com/"+img_name
    local_img_path = 'C:\\juromeocommentimg\\'
    os.makedirs(local_img_path, exist_ok=True)
    r = requests.get(net_img_path)
    with open(local_img_path+img_name, 'wb') as f:
        f.write(r.content)

# 得到每个链接的所有评论
def get_comments(comment_url, sku):
    # url = 'https://www.juromeo.com/getComments?jewelryId=13275'
    db = MySQLdb.connect("192.168.21.238", "root", "Wang123app", "juromeo_comment", charset='utf8')
    cursor = db.cursor()
    response = requests.get(comment_url)  # 请求并获取响应
    if response.text.__len__() > 0:
        jsonstr = json.loads(response.text)  # json解析响应文本
        comments = json.loads(jsonstr['comments'])
        for comment in comments:
            #print(comment)
            if 'author' in comment:
                m_author = comment['author']
            else:
                m_author = ''
            if 'comment' in comment:
                m_comment = comment['comment']
            else:
                m_comment = ''
            if 'commentDate' in comment:
                m_commentDate = comment['commentDate']
            else:
                m_commentDate = ''
            if 'location' in comment:
                m_location = comment['location']
            else:
                m_location = ''
            if 'images' in comment:
                m_images = comment['images']
                img_download(m_images)
            else:
                m_images = ''
            # SQL 插入语句
            sql = "INSERT INTO comments_origin(`product_sku`, `author`, `comment`, `commentDate`, `location`, `images`) VALUES (%s, %s, %s, %s, %s, %s )" % ('"'+sku+'"', '"'+m_author+'"', '"'+m_comment+'"', '"'+m_commentDate+'"', '"'+m_location+'"', '"'+m_images+'"')
            try:
                # 执行sql语句
                print(sql)
                result = cursor.execute(sql)
                print('db result:', result)
                # 提交到数据库执行
                db.commit()
            except:
                # 发生错误时回滚
                db.rollback()
    else:
        print("NO COMMENTS:", sku, comment_url)
    db.close()


file = open('juromeo_comments.txt', 'r')
for line in file:
    url_list = line.split('\t')
    url = url_list[0]
    sku = url_list[1]
    print(url, sku)
    get_comments(url, sku)
    time.sleep(3)
